from pyecharts.charts import Bar
from pyecharts import options as opts
from sc.ZDao import connect_mysql
import pymysql


def generate_min_salary_chart():
    # 连接数据库
    db = connect_mysql()
    if not db:
        print("数据库连接失败")
        return

    try:
        # 创建游标
        with db.cursor() as cur:
            # 查询各城市Java岗位最低薪资（取薪资范围下限）
            sql = """
            SELECT 
                SUBSTRING_INDEX(job_loc, '·', 1) AS city,
                MIN(
                    CASE 
                        WHEN job_sal LIKE '%万%' THEN 
                            CAST(REPLACE(SUBSTRING_INDEX(job_sal, '-', 1), '万', '') AS DECIMAL(10,2)) * 10000
                        WHEN job_sal LIKE '%千%' THEN 
                            CAST(REPLACE(SUBSTRING_INDEX(job_sal, '-', 1), '千', '') AS DECIMAL(10,2)) * 1000
                        WHEN job_sal LIKE '%k%' THEN 
                            CAST(REPLACE(SUBSTRING_INDEX(job_sal, '-', 1), 'k', '') AS DECIMAL(10,2)) * 1000
                        WHEN job_sal REGEXP '[0-9]+元' THEN 
                            CAST(REPLACE(SUBSTRING_INDEX(job_sal, '-', 1), '元', '') AS DECIMAL(10,2))
                        ELSE 999999
                    END
                ) AS min_salary
            FROM jobs
            WHERE (job_name LIKE '%Java%' OR job_name LIKE '%java%')
              AND job_sal NOT LIKE '%面议%'
            GROUP BY SUBSTRING_INDEX(job_loc, '·', 1)
            ORDER BY min_salary DESC
            LIMIT 8
            """
            print("执行SQL:", sql)

            cur.execute(sql)
            results = cur.fetchall()

            # 准备数据
            cities = []
            min_salaries = []
            for row in results:
                cities.append(row['city'])
                min_salaries.append(int(row['min_salary']))
                print(f"城市: {row['city']}, 最低薪资: {row['min_salary']}元")

            # 创建柱状图（完全按照参考图片样式）
            bar = (
                Bar(init_opts=opts.InitOpts(width="800px", height="500px"))
                .add_xaxis(cities)
                .add_yaxis(
                    "月薪水/元",
                    min_salaries,
                    itemstyle_opts=opts.ItemStyleOpts(color="#3366CC"),  # 深蓝色
                    label_opts=opts.LabelOpts(
                        position="top",
                        formatter="{c}",
                        color="#FFFFFF",  # 白色标签
                        font_size=12,
                        font_weight="bold"
                    ),
                    bar_width=40
                )
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title="各城市-最低薪水图",
                        subtitle="单位/元",
                        pos_left="center",
                        title_textstyle_opts=opts.TextStyleOpts(
                            font_size=20,
                            color="#333",
                            font_weight="bold"
                        ),
                        subtitle_textstyle_opts=opts.TextStyleOpts(
                            font_size=12,
                            color="#666"
                        )
                    ),
                    yaxis_opts=opts.AxisOpts(
                        min_=0,
                        max_=18000,  # 固定Y轴最大值
                        interval=3000,  # 固定间隔
                        axislabel_opts=opts.LabelOpts(
                            formatter="{value}",
                            font_size=12
                        ),
                        splitline_opts=opts.SplitLineOpts(
                            is_show=True,
                            linestyle_opts=opts.LineStyleOpts(
                                type_="solid",
                                opacity=0.3
                            )
                        )
                    ),
                    xaxis_opts=opts.AxisOpts(
                        axislabel_opts=opts.LabelOpts(
                            rotate=0,
                            font_size=12
                        )
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        formatter="{b}<br/>最低薪资: {c}元"
                    )
                )
            )

            # 渲染图表
            output_file = "barmin.html"
            bar.render(output_file)
            print(f"\n✅ 最低薪资对比图已生成: {output_file}")

    except pymysql.Error as e:
        print(f"数据库操作出错: {e}")
    finally:
        if db:
            db.close()


if __name__ == "__main__":
    generate_min_salary_chart()